"""
数据库集成测试
=============

测试数据库连接、CRUD操作、事务处理等核心数据持久化功能
"""

import pytest
import asyncio
from unittest.mock import AsyncMock, MagicMock, patch
from datetime import datetime, timezone
from decimal import Decimal
import json

try:
    import asyncpg
    import sqlalchemy as sa
    from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
    from sqlalchemy.orm import sessionmaker
    DATABASE_AVAILABLE = True
except ImportError:
    DATABASE_AVAILABLE = False


@pytest.mark.integration
@pytest.mark.database
class TestDatabaseConnection:
    """数据库连接测试"""
    
    @pytest.fixture
    async def mock_db_engine(self):
        """创建Mock数据库引擎"""
        mock_engine = AsyncMock()
        mock_engine.connect = AsyncMock()
        mock_engine.dispose = AsyncMock()
        
        # Mock连接对象
        mock_connection = AsyncMock()
        mock_connection.execute = AsyncMock()
        mock_connection.close = AsyncMock()
        mock_engine.connect.return_value.__aenter__ = AsyncMock(return_value=mock_connection)
        mock_engine.connect.return_value.__aexit__ = AsyncMock(return_value=None)
        
        return mock_engine
    
    @pytest.fixture
    async def mock_db_session(self):
        """创建Mock数据库会话"""
        mock_session = AsyncMock()
        mock_session.execute = AsyncMock()
        mock_session.commit = AsyncMock()
        mock_session.rollback = AsyncMock()
        mock_session.close = AsyncMock()
        mock_session.add = MagicMock()
        mock_session.delete = MagicMock()
        mock_session.merge = MagicMock()
        
        # Mock查询结果
        mock_result = MagicMock()
        mock_result.scalar_one_or_none = MagicMock(return_value=None)
        mock_result.scalars = MagicMock()
        mock_result.fetchall = MagicMock(return_value=[])
        mock_session.execute.return_value = mock_result
        
        return mock_session
    
    @pytest.mark.asyncio
    async def test_database_engine_creation(self, mock_db_engine):
        """测试数据库引擎创建"""
        # 测试引擎创建
        assert mock_db_engine is not None
        
        # 测试连接
        async with mock_db_engine.connect() as conn:
            assert conn is not None
            mock_db_engine.connect.assert_called_once()
    
    @pytest.mark.asyncio
    async def test_database_connection_pool(self, mock_db_engine):
        """测试数据库连接池"""
        # 模拟多个并发连接
        async def get_connection():
            async with mock_db_engine.connect() as conn:
                await conn.execute("SELECT 1")
                return True
        
        # 创建并发连接任务
        tasks = [get_connection() for _ in range(5)]
        results = await asyncio.gather(*tasks, return_exceptions=True)
        
        # 验证结果
        assert len(results) == 5
        assert all(r is True for r in results)
    
    @pytest.mark.asyncio
    async def test_database_error_handling(self, mock_db_engine):
        """测试数据库错误处理"""
        # 模拟连接错误
        mock_db_engine.connect.side_effect = ConnectionError("Database connection failed")
        
        with pytest.raises(ConnectionError):
            async with mock_db_engine.connect():
                pass


@pytest.mark.integration
@pytest.mark.database
class TestMarketDataRepository:
    """市场数据仓储测试"""
    
    @pytest.fixture
    def mock_market_data_repository(self):
        """创建Mock市场数据仓储"""
        class MockMarketDataRepository:
            def __init__(self):
                self.data = {}
                self.call_count = 0
            
            async def save_market_data(self, market_data):
                """保存市场数据"""
                self.call_count += 1
                symbol = market_data.get('symbol', 'UNKNOWN')
                self.data[symbol] = market_data
                return symbol
            
            async def get_market_data(self, symbol):
                """获取市场数据"""
                self.call_count += 1
                return self.data.get(symbol)
            
            async def get_latest_data(self, symbol, limit=10):
                """获取最新数据"""
                self.call_count += 1
                data = self.data.get(symbol)
                if data:
                    return [data] * min(limit, 1)  # 模拟多条记录
                return []
            
            async def delete_old_data(self, before_date):
                """删除旧数据"""
                self.call_count += 1
                deleted_count = len(self.data)
                self.data.clear()
                return deleted_count
            
            async def get_symbols_list(self):
                """获取符号列表"""
                self.call_count += 1
                return list(self.data.keys())
            
            async def bulk_insert(self, data_list):
                """批量插入"""
                self.call_count += 1
                for data in data_list:
                    symbol = data.get('symbol', 'UNKNOWN')
                    self.data[symbol] = data
                return len(data_list)
        
        return MockMarketDataRepository()
    
    @pytest.mark.asyncio
    async def test_save_market_data(self, mock_market_data_repository):
        """测试保存市场数据"""
        # 准备测试数据
        market_data = {
            "symbol": "AAPL",
            "price": Decimal("150.25"),
            "volume": 1000000,
            "timestamp": datetime.now(timezone.utc).isoformat()
        }
        
        # 测试保存
        result = await mock_market_data_repository.save_market_data(market_data)
        assert result == "AAPL"
        
        # 验证数据保存
        saved_data = await mock_market_data_repository.get_market_data("AAPL")
        assert saved_data is not None
        assert saved_data["symbol"] == "AAPL"
        assert saved_data["price"] == Decimal("150.25")
    
    @pytest.mark.asyncio
    async def test_get_market_data(self, mock_market_data_repository):
        """测试获取市场数据"""
        # 先保存数据
        test_data = {"symbol": "GOOGL", "price": Decimal("2800.50")}
        await mock_market_data_repository.save_market_data(test_data)
        
        # 测试获取
        retrieved_data = await mock_market_data_repository.get_market_data("GOOGL")
        assert retrieved_data is not None
        assert retrieved_data["symbol"] == "GOOGL"
        
        # 测试获取不存在的数据
        non_existent = await mock_market_data_repository.get_market_data("NONEXISTENT")
        assert non_existent is None
    
    @pytest.mark.asyncio
    async def test_get_latest_data(self, mock_market_data_repository):
        """测试获取最新数据"""
        # 保存测试数据
        test_data = {"symbol": "MSFT", "price": Decimal("300.75")}
        await mock_market_data_repository.save_market_data(test_data)
        
        # 测试获取最新数据
        latest_data = await mock_market_data_repository.get_latest_data("MSFT", limit=5)
        assert len(latest_data) == 1
        assert latest_data[0]["symbol"] == "MSFT"
        
        # 测试不存在的符号
        empty_data = await mock_market_data_repository.get_latest_data("NONEXISTENT")
        assert len(empty_data) == 0
    
    @pytest.mark.asyncio
    async def test_bulk_operations(self, mock_market_data_repository):
        """测试批量操作"""
        # 准备批量数据
        bulk_data = [
            {"symbol": "AAPL", "price": Decimal("150.00")},
            {"symbol": "GOOGL", "price": Decimal("2800.00")},
            {"symbol": "MSFT", "price": Decimal("300.00")},
            {"symbol": "TSLA", "price": Decimal("800.00")},
        ]
        
        # 测试批量插入
        insert_count = await mock_market_data_repository.bulk_insert(bulk_data)
        assert insert_count == 4
        
        # 验证数据保存
        symbols = await mock_market_data_repository.get_symbols_list()
        assert len(symbols) == 4
        assert "AAPL" in symbols
        assert "GOOGL" in symbols
        assert "MSFT" in symbols
        assert "TSLA" in symbols
    
    @pytest.mark.asyncio
    async def test_data_cleanup(self, mock_market_data_repository):
        """测试数据清理"""
        # 先保存一些数据
        test_data = [
            {"symbol": "TEST1", "price": Decimal("100.00")},
            {"symbol": "TEST2", "price": Decimal("200.00")},
        ]
        await mock_market_data_repository.bulk_insert(test_data)
        
        # 验证数据存在
        symbols_before = await mock_market_data_repository.get_symbols_list()
        assert len(symbols_before) == 2
        
        # 测试删除旧数据
        deleted_count = await mock_market_data_repository.delete_old_data(datetime.now())
        assert deleted_count == 2
        
        # 验证数据已删除
        symbols_after = await mock_market_data_repository.get_symbols_list()
        assert len(symbols_after) == 0


@pytest.mark.integration
@pytest.mark.database
class TestDatabaseTransactions:
    """数据库事务测试"""
    
    @pytest.fixture
    def mock_transaction_manager(self):
        """创建Mock事务管理器"""
        class MockTransactionManager:
            def __init__(self):
                self.transaction_count = 0
                self.rollback_count = 0
                self.commit_count = 0
                self.data = {}
                self.in_transaction = False
            
            async def begin_transaction(self):
                """开始事务"""
                self.transaction_count += 1
                self.in_transaction = True
                return self
            
            async def commit(self):
                """提交事务"""
                if self.in_transaction:
                    self.commit_count += 1
                    self.in_transaction = False
                    return True
                return False
            
            async def rollback(self):
                """回滚事务"""
                if self.in_transaction:
                    self.rollback_count += 1
                    self.in_transaction = False
                    self.data.clear()  # 模拟回滚数据
                    return True
                return False
            
            async def execute_in_transaction(self, operation, *args, **kwargs):
                """在事务中执行操作"""
                if not self.in_transaction:
                    raise RuntimeError("No active transaction")
                
                # 模拟操作执行
                if operation == "INSERT":
                    key = kwargs.get('key', 'default')
                    value = kwargs.get('value', 'default_value')
                    self.data[key] = value
                    return True
                elif operation == "UPDATE":
                    key = kwargs.get('key')
                    if key in self.data:
                        self.data[key] = kwargs.get('value', self.data[key])
                        return True
                    return False
                elif operation == "DELETE":
                    key = kwargs.get('key')
                    return self.data.pop(key, None) is not None
                elif operation == "ERROR":
                    raise RuntimeError("Simulated database error")
                
                return False
        
        return MockTransactionManager()
    
    @pytest.mark.asyncio
    async def test_successful_transaction(self, mock_transaction_manager):
        """测试成功的事务"""
        # 开始事务
        await mock_transaction_manager.begin_transaction()
        assert mock_transaction_manager.in_transaction
        
        # 执行操作
        result1 = await mock_transaction_manager.execute_in_transaction(
            "INSERT", key="symbol1", value="AAPL"
        )
        result2 = await mock_transaction_manager.execute_in_transaction(
            "INSERT", key="symbol2", value="GOOGL"
        )
        
        assert result1 is True
        assert result2 is True
        assert len(mock_transaction_manager.data) == 2
        
        # 提交事务
        commit_result = await mock_transaction_manager.commit()
        assert commit_result is True
        assert not mock_transaction_manager.in_transaction
        assert mock_transaction_manager.commit_count == 1
    
    @pytest.mark.asyncio
    async def test_transaction_rollback(self, mock_transaction_manager):
        """测试事务回滚"""
        # 开始事务
        await mock_transaction_manager.begin_transaction()
        
        # 执行操作
        await mock_transaction_manager.execute_in_transaction(
            "INSERT", key="test_key", value="test_value"
        )
        assert "test_key" in mock_transaction_manager.data
        
        # 回滚事务
        rollback_result = await mock_transaction_manager.rollback()
        assert rollback_result is True
        assert not mock_transaction_manager.in_transaction
        assert mock_transaction_manager.rollback_count == 1
        assert len(mock_transaction_manager.data) == 0  # 数据应该被清除
    
    @pytest.mark.asyncio
    async def test_transaction_error_handling(self, mock_transaction_manager):
        """测试事务错误处理"""
        # 开始事务
        await mock_transaction_manager.begin_transaction()
        
        # 执行正常操作
        await mock_transaction_manager.execute_in_transaction(
            "INSERT", key="good_key", value="good_value"
        )
        
        # 执行错误操作
        with pytest.raises(RuntimeError, match="Simulated database error"):
            await mock_transaction_manager.execute_in_transaction("ERROR")
        
        # 由于错误，应该回滚
        await mock_transaction_manager.rollback()
        assert len(mock_transaction_manager.data) == 0
    
    @pytest.mark.asyncio
    async def test_nested_transaction_simulation(self, mock_transaction_manager):
        """测试嵌套事务模拟"""
        # 第一个事务
        await mock_transaction_manager.begin_transaction()
        await mock_transaction_manager.execute_in_transaction(
            "INSERT", key="outer", value="outer_value"
        )
        await mock_transaction_manager.commit()
        
        # 第二个事务
        await mock_transaction_manager.begin_transaction()
        await mock_transaction_manager.execute_in_transaction(
            "INSERT", key="inner", value="inner_value"
        )
        await mock_transaction_manager.rollback()  # 这个回滚只影响第二个事务
        
        # 验证事务计数
        assert mock_transaction_manager.transaction_count == 2
        assert mock_transaction_manager.commit_count == 1
        assert mock_transaction_manager.rollback_count == 1


@pytest.mark.integration
@pytest.mark.database
@pytest.mark.performance
class TestDatabasePerformance:
    """数据库性能测试"""
    
    @pytest.fixture
    def mock_performance_db(self):
        """创建性能测试用Mock数据库"""
        class MockPerformanceDB:
            def __init__(self):
                self.query_count = 0
                self.data = {}
                self.query_times = []
            
            async def bulk_insert_performance(self, data_count):
                """批量插入性能测试"""
                import time
                start_time = time.time()
                
                # 模拟批量插入
                for i in range(data_count):
                    self.data[f"symbol_{i}"] = {
                        "symbol": f"SYM{i:04d}",
                        "price": Decimal("100.00") + Decimal(i) * Decimal("0.01"),
                        "volume": 1000 + i
                    }
                
                execution_time = time.time() - start_time
                self.query_times.append(execution_time)
                self.query_count += 1
                return data_count, execution_time
            
            async def bulk_query_performance(self, query_count):
                """批量查询性能测试"""
                import time
                start_time = time.time()
                
                results = []
                for i in range(query_count):
                    key = f"symbol_{i % len(self.data)}" if self.data else "symbol_0"
                    result = self.data.get(key)
                    results.append(result)
                
                execution_time = time.time() - start_time
                self.query_times.append(execution_time)
                self.query_count += query_count
                return len(results), execution_time
            
            async def connection_pool_performance(self, concurrent_connections):
                """连接池性能测试"""
                import time
                start_time = time.time()
                
                # 模拟并发连接
                async def single_connection():
                    await asyncio.sleep(0.001)  # 模拟数据库操作
                    return True
                
                tasks = [single_connection() for _ in range(concurrent_connections)]
                results = await asyncio.gather(*tasks)
                
                execution_time = time.time() - start_time
                return len(results), execution_time
        
        return MockPerformanceDB()
    
    @pytest.mark.asyncio
    async def test_bulk_insert_performance(self, mock_performance_db):
        """测试批量插入性能"""
        # 测试不同数据量的插入性能
        test_sizes = [100, 500, 1000]
        
        for size in test_sizes:
            count, time_taken = await mock_performance_db.bulk_insert_performance(size)
            
            assert count == size
            assert time_taken > 0
            # 性能断言：1000条记录应在1秒内完成
            if size <= 1000:
                assert time_taken < 1.0, f"Bulk insert of {size} records took {time_taken:.3f}s"
    
    @pytest.mark.asyncio
    async def test_bulk_query_performance(self, mock_performance_db):
        """测试批量查询性能"""
        # 先准备数据
        await mock_performance_db.bulk_insert_performance(1000)
        
        # 测试查询性能
        query_count = 500
        result_count, time_taken = await mock_performance_db.bulk_query_performance(query_count)
        
        assert result_count == query_count
        assert time_taken > 0
        # 性能断言：500个查询应在0.5秒内完成
        assert time_taken < 0.5, f"Bulk query of {query_count} records took {time_taken:.3f}s"
    
    @pytest.mark.asyncio
    async def test_connection_pool_performance(self, mock_performance_db):
        """测试连接池性能"""
        # 测试并发连接
        concurrent_count = 50
        result_count, time_taken = await mock_performance_db.connection_pool_performance(concurrent_count)
        
        assert result_count == concurrent_count
        assert time_taken > 0
        # 性能断言：50个并发连接应在2秒内完成
        assert time_taken < 2.0, f"Connection pool test with {concurrent_count} connections took {time_taken:.3f}s"
    
    @pytest.mark.benchmark
    def test_database_operation_benchmark(self, benchmark):
        """数据库操作基准测试"""
        def database_operation():
            # 模拟数据库操作
            data = {}
            for i in range(100):
                data[f"key_{i}"] = f"value_{i}"
            return len(data)
        
        result = benchmark(database_operation)
        assert result == 100


@pytest.mark.integration
@pytest.mark.database
class TestDatabaseMigration:
    """数据库迁移测试"""
    
    @pytest.fixture
    def mock_migration_manager(self):
        """创建Mock迁移管理器"""
        class MockMigrationManager:
            def __init__(self):
                self.current_version = "1.0.0"
                self.applied_migrations = []
                self.available_migrations = [
                    "001_create_market_data_table",
                    "002_add_volume_index",
                    "003_add_timestamp_index",
                    "004_create_user_subscriptions"
                ]
            
            async def get_current_version(self):
                """获取当前数据库版本"""
                return self.current_version
            
            async def get_pending_migrations(self):
                """获取待执行的迁移"""
                return [m for m in self.available_migrations if m not in self.applied_migrations]
            
            async def apply_migration(self, migration_name):
                """应用迁移"""
                if migration_name in self.available_migrations and migration_name not in self.applied_migrations:
                    self.applied_migrations.append(migration_name)
                    return True
                return False
            
            async def rollback_migration(self, migration_name):
                """回滚迁移"""
                if migration_name in self.applied_migrations:
                    self.applied_migrations.remove(migration_name)
                    return True
                return False
            
            async def validate_schema(self):
                """验证数据库模式"""
                # 模拟模式验证
                required_tables = ["market_data", "user_subscriptions"]
                existing_tables = []
                
                if "001_create_market_data_table" in self.applied_migrations:
                    existing_tables.append("market_data")
                if "004_create_user_subscriptions" in self.applied_migrations:
                    existing_tables.append("user_subscriptions")
                
                return all(table in existing_tables for table in required_tables)
        
        return MockMigrationManager()
    
    @pytest.mark.asyncio
    async def test_migration_version_check(self, mock_migration_manager):
        """测试迁移版本检查"""
        version = await mock_migration_manager.get_current_version()
        assert version == "1.0.0"
        
        pending = await mock_migration_manager.get_pending_migrations()
        assert len(pending) == 4  # 所有迁移都待执行
    
    @pytest.mark.asyncio
    async def test_apply_migrations(self, mock_migration_manager):
        """测试应用迁移"""
        # 应用第一个迁移
        result1 = await mock_migration_manager.apply_migration("001_create_market_data_table")
        assert result1 is True
        
        # 检查待执行迁移数量减少
        pending = await mock_migration_manager.get_pending_migrations()
        assert len(pending) == 3
        
        # 应用所有迁移
        for migration in pending.copy():
            await mock_migration_manager.apply_migration(migration)
        
        # 验证所有迁移都已应用
        final_pending = await mock_migration_manager.get_pending_migrations()
        assert len(final_pending) == 0
    
    @pytest.mark.asyncio
    async def test_migration_rollback(self, mock_migration_manager):
        """测试迁移回滚"""
        # 先应用一个迁移
        await mock_migration_manager.apply_migration("001_create_market_data_table")
        assert "001_create_market_data_table" in mock_migration_manager.applied_migrations
        
        # 回滚迁移
        rollback_result = await mock_migration_manager.rollback_migration("001_create_market_data_table")
        assert rollback_result is True
        assert "001_create_market_data_table" not in mock_migration_manager.applied_migrations
    
    @pytest.mark.asyncio
    async def test_schema_validation(self, mock_migration_manager):
        """测试数据库模式验证"""
        # 初始状态：模式无效
        is_valid = await mock_migration_manager.validate_schema()
        assert is_valid is False
        
        # 应用必要的迁移
        await mock_migration_manager.apply_migration("001_create_market_data_table")
        await mock_migration_manager.apply_migration("004_create_user_subscriptions")
        
        # 验证模式有效
        is_valid_after = await mock_migration_manager.validate_schema()
        assert is_valid_after is True
